package com.bckj.fastboot.core.sql;

import com.baomidou.mybatisplus.annotation.FieldStrategy;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;
import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
import com.bckj.fastboot.core.lang.enums.OperatorEnum;
import com.bckj.fastboot.core.sql.meta.*;
import com.bckj.fastboot.core.util.BeanAccessor;
import com.bckj.fastboot.core.util.BeanAccessorProvider;
import com.bckj.fastboot.core.util.ConvertUtils;
import com.bckj.fastboot.core.util.SpringUtils;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.jdbc.SQL;

import java.lang.reflect.Field;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.function.Predicate;
import java.util.stream.Collectors;

@Slf4j
public class SqlQueryBuilder implements SqlConditionBuilder<SqlQueryBuilder> {


    private static final Predicate<TableFieldInfo> DEFALUT_FIELDS_SELECT_PREDICATE = p -> true;

    private static final Predicate<Column> DEFALUT_SELECT_PREDICATE = p -> true;

    private static final Function<String, String> DEFAULT_SELECT_MAPPING = p -> p;


    private final SQL sql;

    private String cteSql;

    private String preSql;

    private String lastSql;

    private String customSql;

    @Setter
    private FieldStrategy whereStrategy;

    private AtomicInteger paramNameSeq;

    @Getter
    private Map<String, Object> paramNameValuePairs;

    public SqlQueryBuilder() {
        sql = new SQL();
        init();
    }

    private void init() {
        try {
            MybatisPlusProperties mybatisPlusProperties = SpringUtils.getBean(MybatisPlusProperties.class);
            whereStrategy = mybatisPlusProperties.getGlobalConfig().getDbConfig().getWhereStrategy();
        } catch (Exception e) {
            log.error(e.getMessage());
            //任何时候都加入SQL，保证数据安全
            whereStrategy = FieldStrategy.ALWAYS;
        }

        // 必要属性初始化
        paramNameSeq = new AtomicInteger(0);
        paramNameValuePairs = new HashMap<>(16);
    }

    public SqlQueryBuilder whereAlways() {
        whereStrategy = FieldStrategy.ALWAYS;
        return this;
    }

    public SqlQueryBuilder whereNotNull() {
        whereStrategy = FieldStrategy.NOT_NULL;
        return this;
    }

    public SqlQueryBuilder whereNotEmpty() {
        whereStrategy = FieldStrategy.NOT_EMPTY;
        return this;
    }

    public SqlQueryBuilder with(String name, String colName, String subQuery) {
        cteSql = String.format("WITH %s (%s) AS (%s)", name, colName, subQuery);
        return this;
    }

    public SqlQueryBuilder select(TableLike table) {
        return select(table.getColumns());
    }

    public SqlQueryBuilder select(Collection<? extends Expression> columns) {
        sql.SELECT(columns.stream().map(Expression::toString).collect(Collectors.joining(",")));
        return this;
    }

    public SqlQueryBuilder select(Expression... columns) {
        select(List.of(columns));
        return this;
    }

    public SqlQueryBuilder select(String... columns) {
        sql.SELECT(columns);
        return this;
    }

    public SqlQueryBuilder selectFormat(String format, Object... args) {
        args = Arrays.stream(args).map(p -> {
            if (p instanceof Column column) {
                return column.getTableColumnName();
            }
            return p.toString();
        }).toArray();
        sql.SELECT(format.formatted(args));
        return this;
    }

    public SqlQueryBuilder selectTableColumns(TableLike table, Column... columns) {
        if (table.getAlias() == null) {
            return select(columns);
        } else {
            String collect = Arrays.stream(columns).map(p -> {
                if (p.getAlias() == null) {
                    return "%s.%s".formatted(table.getAlias(), p.getColumn());
                } else {
                    return "%s.%s AS %s".formatted(table.getAlias(), p.getColumn(), p.getAlias());
                }
            }).collect(Collectors.joining(","));
            sql.SELECT(collect);
            return this;
        }
    }

    public SqlQueryBuilder selectAllFields(Table table) {
        return selectAllFields(table, DEFALUT_FIELDS_SELECT_PREDICATE, DEFAULT_SELECT_MAPPING);
    }

    public SqlQueryBuilder selectAllFields(Table table, Predicate<TableFieldInfo> selectPredicate, Function<String, String> selectMapping) {
        if (selectPredicate == null) {
            selectPredicate = DEFALUT_FIELDS_SELECT_PREDICATE;
        }
        if (selectMapping == null) {
            selectMapping = DEFAULT_SELECT_MAPPING;
        }
        String str = table.getTableInfo().getFieldList()
                .stream()
                .filter(selectPredicate)
                .map(p -> table.getAlias() == null ? p.getColumn() : table.getAlias() + "." + p.getColumn())
                .map(selectMapping)
                .collect(Collectors.joining(","));
        sql.SELECT(str);
        return this;
    }

    public SqlQueryBuilder selectAllColumns(TableLike table) {
        return selectAllColumns(table, DEFALUT_SELECT_PREDICATE, DEFAULT_SELECT_MAPPING);
    }

    public SqlQueryBuilder selectAllColumns(TableLike table, Predicate<Column> selectPredicate, Function<String, String> selectMapping) {
        if (selectPredicate == null) {
            selectPredicate = DEFALUT_SELECT_PREDICATE;
        }
        if (selectMapping == null) {
            selectMapping = DEFAULT_SELECT_MAPPING;
        }
        String str = table.getColumns()
                .stream()
                .filter(selectPredicate)
                .map(p -> table.getAlias() == null ? p.getColumn() : table.getAlias() + "." + p.getColumn())
                .map(selectMapping)
                .collect(Collectors.joining(","));
        sql.SELECT(str);
        return this;
    }

    public SqlQueryBuilder from(String table) {
        sql.FROM(table);
        return this;
    }

    public SqlQueryBuilder from(TableLike table) {
        sql.FROM(table.getTableName() + " AS " + table.getAlias());
        return this;
    }

    public SqlQueryBuilder join(String join) {
        sql.JOIN(join);
        return this;
    }

    public SqlQueryBuilder join(TableLike table, Column l, Column r, String otherWhere) {
        return join(buildJoin(table, l, r, otherWhere));
    }

    public SqlQueryBuilder join(TableLike table, Column l, Column r) {
        return join(buildJoin(table, l, r, null));
    }

    public SqlQueryBuilder innerJoin(String join) {
        sql.INNER_JOIN(join);
        return this;
    }

    public SqlQueryBuilder innerJoin(TableLike table, Column l, Column r, String otherWhere) {
        return innerJoin(buildJoin(table, l, r, otherWhere));
    }

    public SqlQueryBuilder innerJoin(TableLike table, Column l, Column r) {
        return innerJoin(buildJoin(table, l, r, null));
    }

    public SqlQueryBuilder innerJoin(TableLike table, Consumer<SqlConditionBuilder<?>> consumer) {
        return innerJoin(buildJoin(table, consumer));
    }

    public SqlQueryBuilder leftJoin(String join) {
        sql.LEFT_OUTER_JOIN(join);
        return this;
    }

    public SqlQueryBuilder leftJoin(TableLike table, Column l, Column r, String otherWhere) {
        return leftJoin(buildJoin(table, l, r, otherWhere));
    }

    public SqlQueryBuilder leftJoin(TableLike table, Column l, Column r) {
        return leftJoin(buildJoin(table, l, r, null));
    }

    public SqlQueryBuilder leftJoin(TableLike table, Consumer<SqlConditionBuilder<?>> consumer) {
        return leftJoin(buildJoin(table, consumer));
    }

    public SqlQueryBuilder rightJoin(String join) {
        sql.RIGHT_OUTER_JOIN(join);
        return this;
    }

    public SqlQueryBuilder rightJoin(TableLike table, Column l, Column r, String otherWhere) {
        return rightJoin(buildJoin(table, l, r, otherWhere));
    }

    public SqlQueryBuilder rightJoin(TableLike table, Column l, Column r) {
        return rightJoin(buildJoin(table, l, r, null));
    }

    public SqlQueryBuilder rightJoin(TableLike table, Consumer<SqlConditionBuilder<?>> consumer) {
        return rightJoin(buildJoin(table, consumer));
    }

    public SqlQueryBuilder outerJoin(String join) {
        sql.OUTER_JOIN(join);
        return this;
    }

    public SqlQueryBuilder outerJoin(TableLike table, Column l, Column r, String otherWhere) {
        return outerJoin(buildJoin(table, l, r, otherWhere));
    }

    public SqlQueryBuilder outerJoin(TableLike table, Column l, Column r) {
        return outerJoin(buildJoin(table, l, r, null));
    }

    public SqlQueryBuilder outerJoin(TableLike table, Consumer<SqlConditionBuilder<?>> consumer) {
        return outerJoin(buildJoin(table, consumer));
    }

    private String buildJoin(TableLike table, Column l, Column r, String otherWhere) {
        String alias = table.getAlias();
        String join = String.format("%s AS %s ON %s = %s", table.getTableName(), alias, l.getTableColumnName(), r.getTableColumnName());
        if (otherWhere != null) {
            join += " " + otherWhere;
        }
        return join;
    }

    private String buildJoin(TableLike table, Consumer<SqlConditionBuilder<?>> consumer) {
        NestedConditionBuilder conditionBuilder = new NestedConditionBuilder();
        consumer.accept(conditionBuilder);
        String alias = table.getAlias();
        return String.format("%s AS %s ON %s", table.getTableName(), alias, conditionBuilder);
    }

    @Override
    public SqlQueryBuilder where(String field, Object value, String opt) {
        if (checkValue(value)) {
            sql.WHERE(String.format("%s %s %s", field, opt, formatValue(value)));
        }
        return this;
    }

    public SqlQueryBuilder where(Column column, Object value, String opt) {
        return where(column.getTableColumnName(), opt, formatValue(value));
    }

    @Override
    public SqlQueryBuilder eq(String field, Object value) {
        return where(field, value, " = ");
    }

    @Override
    public SqlQueryBuilder ne(String field, Object value) {
        return where(field, value, " <> ");
    }

    @Override
    public SqlQueryBuilder gt(String field, Object value) {
        return where(field, value, " > ");
    }

    @Override
    public SqlQueryBuilder ge(String field, Object value) {
        return where(field, value, " >= ");
    }

    @Override
    public SqlQueryBuilder lt(String field, Object value) {
        return where(field, value, " < ");
    }

    @Override
    public SqlQueryBuilder le(String field, Object value) {
        return where(field, value, " <= ");
    }

    @Override
    public SqlQueryBuilder like(String field, Object value) {
        if (checkValue(value)) {
            sql.WHERE(field + " LIKE CONCAT('%'," + formatValue(value) + ",'%')");
        }
        return this;
    }

    @Override
    public SqlQueryBuilder likeLeft(String field, Object value) {
        if (checkValue(value)) {
            sql.WHERE(field + " LIKE CONCAT('%'," + formatValue(value) + ")");
        }
        return this;
    }

    @Override
    public SqlQueryBuilder likeRight(String field, Object value) {
        if (checkValue(value)) {
            sql.WHERE(field + " LIKE CONCAT(" + formatValue(value) + ",'%')");
        }
        return this;
    }

    @Override
    public SqlQueryBuilder between(String field, Object val1, Object val2) {
        if (checkValue(val1) && checkValue(val2)) {
            sql.WHERE(String.format("%s BETWEEN %s AND %s", field, formatValue(val1), formatValue(val2)));
        }
        return this;
    }

    @Override
    public SqlQueryBuilder in(String field, Collection<?> coll) {
        if (CollectionUtils.isNotEmpty(coll)) {
            sql.WHERE(String.format("%s IN (%s)", field, coll.stream().map(this::formatValue).collect(Collectors.joining(","))));
        }
        return this;
    }

    @Override
    public SqlQueryBuilder notIn(String field, Collection<?> coll) {
        if (CollectionUtils.isNotEmpty(coll)) {
            sql.WHERE(String.format("%s NOT IN (%s)", field, coll.stream().map(this::formatValue).collect(Collectors.joining(","))));
        }
        return this;
    }

    @Override
    public SqlQueryBuilder isNull(String column) {
        sql.WHERE(column + " IS NULL");
        return this;
    }

    @Override
    public SqlQueryBuilder isNotNull(String column) {
        sql.WHERE(column + " IS NOT NULL");
        return this;
    }

    @Override
    public SqlQueryBuilder and(Consumer<SqlConditionBuilder<?>> consumer) {
        NestedConditionBuilder builder = new NestedConditionBuilder();
        consumer.accept(builder);
        sql.WHERE(builder.toString());
        return this;
    }


    @SuppressWarnings("unchecked")
    public <T> SqlQueryBuilder filterByExample(T example) {
        Class<T> exampleClass = (Class<T>) example.getClass();
        Field[] fields = exampleClass.getDeclaredFields();
        BeanAccessor<T> beanAccessor = BeanAccessorProvider.get(exampleClass);
        for (Field field : fields) {
            String column;
            OperatorEnum operator = OperatorEnum.EQ;
            QueryMapping anno = field.getAnnotation(QueryMapping.class);
            if (anno != null) {
                if (!anno.exist()) {
                    continue;
                }
                column = anno.value();
                operator = anno.operator();
            } else {
                column = ConvertUtils.camelCaseToUnderline(field.getName());
            }
            Object value = beanAccessor.getValue(example, field.getName()).orElse(null);
            if (value != null) {
                switch (operator) {
                    case EQ -> eq(column, value);
                    case NE -> ne(column, value);
                    case GT -> gt(column, value);
                    case GE -> ge(column, value);
                    case LT -> lt(column, value);
                    case LE -> le(column, value);
                    case LIKE -> like(column, value);
                    case LIKE_LEFT -> likeLeft(column, value);
                    case LIKE_RIGHT -> likeRight(column, value);
                    case BETWEEN -> {
                        if (value instanceof Object[] values) {
                            if (values.length != 2) {
                                throw new IllegalArgumentException("between value must be two");
                            }
                            between(column, values[0], values[1]);
                        } else {
                            throw new IllegalArgumentException("between value must be Object[]");
                        }
                    }
                    case IN -> in(column, (Collection<?>) value);
                    case NOT_IN -> notIn(column, (Collection<?>) value);
                    case IS_NULL -> isNull(column);
                    case IS_NOT_NULL -> isNotNull(column);
                }
            }
        }
        return this;
    }


    public SqlQueryBuilder groupBy(String columns) {
        sql.GROUP_BY(columns);
        return this;
    }

    public SqlQueryBuilder groupBy(Column... columns) {
        if (columns == null || columns.length == 0) {
            throw new IllegalArgumentException("columns is empty");
        }
        if (columns.length == 1) {
            sql.GROUP_BY(columns[0].getTableColumnName());
        } else {
            sql.GROUP_BY(Arrays.stream(columns).map(Column::getTableColumnName).collect(Collectors.joining(",")));
        }
        return this;
    }

    public SqlQueryBuilder orderBy(String columns) {
        sql.ORDER_BY(columns);
        return this;
    }

    public SqlQueryBuilder orderBy(boolean isAsc, Column... columns) {
        if (columns == null || columns.length == 0) {
            throw new IllegalArgumentException("columns is empty");
        }
        if (columns.length == 1) {
            sql.ORDER_BY(columns[0].getTableColumnName() + (isAsc ? "" : " DESC"));
        } else {
            sql.ORDER_BY(Arrays.stream(columns).map(i -> i.getTableColumnName() + (isAsc ? "" : " DESC")).collect(Collectors.joining(",")));
        }
        return this;
    }

    @Override
    public SqlQueryBuilder getChildren() {
        return this;
    }

    public SqlQueryBuilder setPreSql(String preSql) {
        this.preSql = preSql;
        return this;
    }

    public SqlQueryBuilder setLastSql(String lastSql) {
        this.lastSql = lastSql;
        return this;
    }

    /**
     * 自定义SQL，如果需要动态条件，则有且只有一个%s
     * 例如：
     * select * from user
     * %s
     * limit 10
     */
    public SqlQueryBuilder customSql(String customSql) {
        this.customSql = customSql;
        sql.SELECT("__");
        return this;
    }

    @Override
    public String toString() {
        String str = sql.toString();
        if (customSql != null) {
            return customSql.formatted(str.substring(10));
        }
        if (preSql != null) {
            str = preSql + ' ' + str;
        }
        if (lastSql != null) {
            str = str + ' ' + lastSql;
        }
        if (cteSql != null) {
            str = cteSql + "\n" + str;
        }
        return str;
    }

    public TableLike toTable(String alias) {
        return new Table("(" + this + ")", alias);
    }

    private boolean checkValue(Object value) {
        return switch (whereStrategy) {
            case ALWAYS -> true;
            case NEVER -> false;
            case NOT_EMPTY -> {
                if (value == null) {
                    yield false;
                }
                if (value instanceof String s) {
                    yield !s.trim().isEmpty();
                } else if (value instanceof Collection<?> c) {
                    yield !c.isEmpty();
                } else if (value instanceof Map<?, ?> m) {
                    yield !m.isEmpty();
                }
                yield true;
            }
            default -> value != null;
        };
    }

    private String formatValue(Object value) {
        if (value == null) {
            return "NULL";
        }
        if (value instanceof Column  column) {
            return column.getTableColumnName();
        }
        String genParamName = "MPGENVAL" + this.paramNameSeq.incrementAndGet();
        String paramStr = "sw.paramNameValuePairs." + genParamName;
        this.paramNameValuePairs.put(genParamName, value);
        return SqlScriptUtils.safeParam(paramStr, null);
    }

    public class NestedConditionBuilder implements SqlConditionBuilder<NestedConditionBuilder> {
        private static final String OR = " \nOR ";

        private final List<String> where = new ArrayList<>();
        private List<String> lastList = new ArrayList<>();

        @Override
        public NestedConditionBuilder getChildren() {
            return this;
        }

        private void buildWhere(StringBuilder builder, List<String> parts) {
            if (!parts.isEmpty()) {
                if (!builder.isEmpty()) {
                    builder.append("\n");
                }
                builder.append("(");
                String last = "________";
                for (int i = 0, n = parts.size(); i < n; i++) {
                    String part = parts.get(i);
                    if (i > 0 && !part.equals(OR) && !last.equals(OR)) {
                        builder.append(" AND ");
                    }
                    builder.append(part);
                    last = part;
                }
                builder.append(")");
            }
        }

        private void where(String conditions) {
            where.add(conditions);
            lastList = where;
        }

        @Override
        public NestedConditionBuilder where(String field, Object value, String opt) {
            if (checkValue(value)) {
                where(String.format("%s %s %s", field, opt, formatValue(value)));
            }
            return this;
        }

        @Override
        public NestedConditionBuilder eq(String field, Object value) {
            return where(field, value, " = ");
        }

        @Override
        public NestedConditionBuilder ne(String field, Object value) {
            return where(field, value, " != ");
        }

        @Override
        public NestedConditionBuilder gt(String field, Object value) {
            return where(field, value, " > ");
        }

        @Override
        public NestedConditionBuilder ge(String field, Object value) {
            return where(field, value, " >= ");
        }

        @Override
        public NestedConditionBuilder lt(String field, Object value) {
            return where(field, value, " < ");
        }

        @Override
        public NestedConditionBuilder le(String field, Object value) {
            return where(field, value, " <= ");
        }

        @Override
        public NestedConditionBuilder like(String field, Object value) {
            if (checkValue(value)) {
                where(field + " LIKE CONCAT('%'," + formatValue(value) + ",'%')");
            }
            return this;
        }

        @Override
        public NestedConditionBuilder likeLeft(String field, Object value) {
            if (checkValue(value)) {
                where(field + " LIKE CONCAT('%'," + formatValue(value) + ")");
            }
            return this;
        }

        @Override
        public NestedConditionBuilder likeRight(String field, Object value) {
            if (checkValue(value)) {
                where(field + " LIKE CONCAT(" + formatValue(value) + ",'%')");

            }
            return this;
        }

        @Override
        public NestedConditionBuilder between(String field, Object val1, Object val2) {
            if (checkValue(val1) && checkValue(val2)) {
                where(String.format("%s BETWEEN %s AND %s", field, formatValue(val1), formatValue(val2)));
            }
            return this;
        }

        @Override
        public NestedConditionBuilder in(String field, Collection<?> coll) {
            if (CollectionUtils.isNotEmpty(coll)) {
                where(String.format("%s IN (%s)", field, coll.stream().map(SqlQueryBuilder.this::formatValue).collect(Collectors.joining(","))));
            }
            return this;
        }

        @Override
        public NestedConditionBuilder notIn(String field, Collection<?> coll) {
            if (CollectionUtils.isNotEmpty(coll)) {
                where(String.format("%s NOT IN (%s)", field, coll.stream().map(SqlQueryBuilder.this::formatValue).collect(Collectors.joining(","))));
            }
            return this;
        }

        @Override
        public NestedConditionBuilder isNull(String column) {
            where(column + " IS NULL");
            return this;
        }

        @Override
        public NestedConditionBuilder isNotNull(String column) {
            where(column + " IS NOT NULL");
            return this;
        }

        @Override
        public NestedConditionBuilder and(Consumer<SqlConditionBuilder<?>> consumer) {
            NestedConditionBuilder builder = new NestedConditionBuilder();
            consumer.accept(builder);
            where.add(builder.toString());
            return this;
        }

        @Override
        public NestedConditionBuilder or() {
            lastList.add(OR);
            return this;
        }

        @Override
        public String toString() {
            StringBuilder builder = new StringBuilder();
            buildWhere(builder, where);
            return builder.toString();
        }
    }
}
